package com.boss.thirteen.service.impl;

import com.boss.salary.dao.IAgencyInfoDao;
import com.boss.salary.dao.IPersonTypeSalaryDao;
import com.boss.salary.entity.PersonTypeSalary;
import com.boss.salary.service.IRedisService;
import com.boss.salary.service.impl.SalaryHistoryAsyncServiceImpl;
import com.boss.salary.vo.AgencySalaryVO;
import com.boss.thirteen.dao.ISalaryThirteenMonthDao;
import com.boss.thirteen.entity.SalaryThirteenMonth;
import com.singularsys.jep.JepException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Async;
import org.springframework.scheduling.annotation.EnableAsync;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import java.io.BufferedInputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.*;
import java.util.stream.Collectors;


@Service
@EnableAsync
public class ThirteenSalaryExcelAsyn {
    private static final Logger logger = LoggerFactory.getLogger(SalaryHistoryAsyncServiceImpl.class);

    @Autowired
    private IAgencyInfoDao iAgencyInfoDao;
    @Autowired
    private ISalaryThirteenMonthDao salaryThirteenMonthDao;
    @Autowired
    private IRedisService redisService;
    @Autowired
    private IPersonTypeSalaryDao iPersonTypeSalaryDao;

    @Async
    @Transactional(rollbackFor = Exception.class)
    public void ReadAsync(BufferedInputStream fis,String fileName, String province, String agencyId, List<AgencySalaryVO> exccelColumnName)  throws Exception {

        String code  = iAgencyInfoDao.getCodeByAgencyId(agencyId);
        List<String> slist= new ArrayList<>();


        String substring = fileName.substring(fileName.lastIndexOf("."));

        try{
            //判断版本（07，03）
            Workbook workbook = ".xls".equals(substring) ? new HSSFWorkbook(fis) : new XSSFWorkbook(fis);
            readExcel(province, agencyId, code, exccelColumnName, slist, workbook);
        }catch (Exception e){
            redisService.put(province+agencyId,"F-文件格式有误，请上传excel文件");
            throw e;
        }finally {
            if(null!=fis) {
                fis.close();
            }
        }
    }
    private void readExcel(String province, String agencyId, String code,
                           List<AgencySalaryVO> exccelColumnName, List<String> slist,
                           Workbook workbook) throws Exception {
        List<PersonTypeSalary> columnNameList=iPersonTypeSalaryDao.getInfoByPersonTypes4Unit(null,province);
        Map<String,String> columnPersonTypeMap=columnNameList.stream().collect(Collectors.toMap(PersonTypeSalary::getColumnName, PersonTypeSalary::getPersonTypeCode));
        Map<String,String> columnSalaryName=columnNameList.stream().collect(Collectors.toMap(PersonTypeSalary::getColumnName, PersonTypeSalary::getSalaryName));
        List<SalaryThirteenMonth> salaryThirteenMonthVOs =new ArrayList<>();
        Map<String,Object> params=new HashMap<>();
        params.put("agencyId",agencyId);
        params.put("province",province);
        Sheet sheet = workbook.getSheetAt(0);
        Row rowTitle = sheet.getRow(0);
        String personName="";
        StringBuffer requestMessage=new StringBuffer();
        if (rowTitle!=null) {
            //获取有效标题头
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                Cell cell = rowTitle.getCell(cellNum);
                if (cell != null) {
                    String cellValue = cell.getStringCellValue();
                    for (int i = 0; i < exccelColumnName.size(); i++) {
                        if(exccelColumnName.get(i).getSalaryName().equals(cellValue)){
                            slist.add(exccelColumnName.get(i).getColumnName());
                        }
                    }
                }
            }
            if(slist.size()!=cellCount-4){
                redisService.put(province+agencyId,"F-存在无法读取的工资项");
                return;
            }
        }

        // 获取表中的内容
        int rowCount = sheet.getPhysicalNumberOfRows();
        if(rowCount<=1){
            redisService.put(province+agencyId,"F-文件无内容");
            return;
        }

        //通过agencyid查询这个单位下的所有工资项
       List<SalaryThirteenMonth> salaryMonthEndVOS=salaryThirteenMonthDao.selectSalaryThirteenMonth(params);
        for (int rowNum = 1; rowNum < rowCount ; rowNum++) {
            Row rowData = sheet.getRow(rowNum);
            SalaryThirteenMonth salaryMonthEndVO= new SalaryThirteenMonth();
            if (rowData!=null){
                // 读取列
                int cellCount = rowTitle.getPhysicalNumberOfCells();

                for (int cellNum = 0; cellNum < cellCount ; cellNum++) {
                    if(cellNum==1){
                        Cell cell = rowData.getCell(cellNum);
                        String code1=getCellValue(cell).toString();
                        String[] array=code1.split("\\.");
                        code1=array[0];
                        if(!code.equals(code1)){
                            redisService.put(province+agencyId,"F-第"+(rowNum+1)+"行单位编码有误");
                            return;
                        }else{
                            salaryMonthEndVO.setAgencyId(agencyId);
                        }
                    }
                    if(cellNum==2){
                        Cell cell = rowData.getCell(cellNum);
                        String idenNo=getCellValue(cell).toString();
                        //循环 salaryMonthEndFor命名
                        salaryMonthEndFor:for(SalaryThirteenMonth salaryMonthEndVO1:salaryMonthEndVOS){
                            //判断idenNo不为空并且 和idenNo相等
                        if(!StringUtils.isEmpty(idenNo)&&idenNo.equals(salaryMonthEndVO1.getIdenNo())){
                            salaryMonthEndVO=salaryMonthEndVO1;
                            break salaryMonthEndFor;
                        }
                        }
                        if(StringUtils.isEmpty(salaryMonthEndVO.getPersonId())){
                            redisService.put(province+agencyId,"F-第"+(rowNum+1)+"行身份证号有误");
                            return;
                        }
                    }
                    if(cellNum==3){
                        Cell cell = rowData.getCell(cellNum);
                        personName=getCellValue(cell).toString();
                    }
                    if(cellNum>3){
                        Cell cell = rowData.getCell(cellNum);
                        Class<?> clazz = salaryMonthEndVO.getClass();
                        String strCell;
                        try {
                            if(cell==null){
                                strCell ="0";
                            }else if(cell.getCellTypeEnum()==CellType.STRING){
                                strCell = String.valueOf(cell.getStringCellValue());
                            }else{
                                strCell = String.valueOf(cell.getNumericCellValue());
                            }

                        } catch (IllegalStateException e) {
                            if(cell==null){
                                strCell ="0";
                            }else if(cell.getCellTypeEnum()==CellType.STRING){
                                strCell = String.valueOf(cell.getStringCellValue());
                            }else{
                                strCell = String.valueOf(cell.getNumericCellValue());
                            }
                        }
                        if(strCell.startsWith("-")){
                            redisService.put(province+agencyId,"F-"+strCell+"数据有误，请勿填写负数");
                            return;
                        }
                        if(strCell.length()>1&&strCell.startsWith("0")&&!strCell.substring(1,2).equals(".")){
                            redisService.put(province+agencyId,"F-"+strCell+"数据格式有误，请检查");
                            return;
                        }
                        try {

                            if(Arrays.asList(columnPersonTypeMap.get(slist.get(cellNum-4)).split(",")).contains(salaryMonthEndVO.getMeritPayCode()) ){
                                                Method setMethod = clazz.getDeclaredMethod("set" +
                                                        slist.get(cellNum-4).substring(0, 1).toUpperCase() +  slist.get(cellNum-4).substring(1),Double.class);
                                                setMethod.invoke(salaryMonthEndVO,Double.valueOf(strCell));
                            }else if(Double.valueOf(strCell)!= 0){
                                if(requestMessage.length()==0){
                                    requestMessage.append("W-");
                                }
                                requestMessage.append("人员").append(personName).append(" 没有工资项 “").append(columnSalaryName.get(slist.get(cellNum-4))).append("”，但导入文件中存在金额，请检查</br>");
                            }
                        }catch (Exception e){
                            redisService.put(province+agencyId,"F-"+strCell+"数据格式有误，请检查");
                            return;
                        }

                    }
                }
                salaryThirteenMonthVOs.add(salaryMonthEndVO);
            }
        }
        //todo 计算
        computeSalary(SalaryThirteenMonth.class,salaryThirteenMonthVOs);

        DecimalFormat df=new DecimalFormat("0.00");
        for(int i=0;i<salaryThirteenMonthVOs.size();i++) {
            salaryThirteenMonthDao.updateSalaryThirteenMonth(salaryThirteenMonthVOs.get(i));
            if(i+1==salaryThirteenMonthVOs.size()){
                if(requestMessage.length()>0){
                    redisService.put(province+agencyId, requestMessage.toString());
                }else {
                    redisService.put(province + agencyId, "S");
                }
            }else {
                redisService.put(province + agencyId, df.format((float) (i + 1) / salaryThirteenMonthVOs.size()));
            }
        }
    }
    
    private void computeSalary(Class<?> clazz, List<SalaryThirteenMonth> salaryMonthEndVOs) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException, JepException {
        for(SalaryThirteenMonth stm:salaryMonthEndVOs) {
            BigDecimal pecent = new BigDecimal(stm.getPercent());
            BigDecimal resultValue = new BigDecimal("0.00");
            for (int i = 3; i < 100; i++) {

                Method getMethod = clazz.getMethod("getS" + i);
                Object value = getMethod.invoke(stm);
                //所有输入数值先进行保留两位小数操作，然后再参加计算
                resultValue = resultValue.add(new BigDecimal(StringUtils.isEmpty(value) ? "0.00" : value.toString())).setScale(2, BigDecimal.ROUND_HALF_UP);

            }
            resultValue = resultValue.multiply(pecent).setScale(2, BigDecimal.ROUND_HALF_UP);
            Method setMethod = clazz.getMethod("setS1", Double.class);
            setMethod.invoke(stm, resultValue.doubleValue());
        }
    }

    public static Object getCellValue(Cell cell) {
        NumberFormat numberFormat = NumberFormat.getInstance();
        numberFormat.setGroupingUsed(false);
        if (cell==null || cell.toString().trim().equals("")) {
            return "0";
        }
        Object cellValue = null;
        CellType cellType = cell.getCellTypeEnum();// CellType.forInt(cell.getCellType());
        if (cellType == CellType.STRING) {
            cellValue = cell.getStringCellValue();
        } else if (cellType == CellType.NUMERIC) {
            cellValue =numberFormat.format(cell.getNumericCellValue());
        } else if (cellType == CellType.BOOLEAN) {
            cellValue = cell.getBooleanCellValue();
        } else if (cellType == CellType.FORMULA) {
            cellValue = cell.getCellFormula();
        } else if (cellType == CellType.BLANK) {
            cellValue = "0";
        }
        return cellValue;
    }

}
